﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Login.ADL
{
    public class tb_Result
    {
        private static readonly string conter = ConfigurationManager.ConnectionStrings["consting"].ConnectionString;

        /// <summary>
        /// 班级上课周表格提取
        /// </summary>
        /// <returns></returns>
        #region
        public static DataSet getData()
        {
            //构造按时间顺序排课的sql语句
            string sql = "select b.ClassName,c.groupName,d.TimeName,a.Week2,a.Week3,a.Week4,a.Week5,a.Week6,a.Week7,a.Week8,a.Week9,a.Week10,a.Week11,a.Week12,a.Week13,a.Week14,a.Week15,a.Week16,a.Week17  from tb_NoClass a inner join tb_Class b on a.ClassId=b.ClassId  inner join tb_Group c on a.GroupId=c.GroupId inner join tb_Time d on a.TimeId=d.TimeId order by a.TimeId";
            //提取排课结果表
            DataSet ds = Helpsql.getdata(sql);
            return ds;
        }
        #endregion

        //同时间班级上课对撞时间
        #region
        public static string gettime(string banjiid)
        {
            string sql = "select TimeId from tb_NoClass where ClassId='{0}' and GroupId='1'";
            sql = string.Format(sql,banjiid);
            string shijian = Helpsql.getgeti(sql);
            return shijian;
        }
        public static void duizhuang(string banjiid)
        {
            string shijian = gettime(banjiid);
            for (int i = 2; i < 17; i++)
            {
                string week = "Week" + i;
                for (int n = 1; n < 3; n++)
                {
                    string sql = "select {0} from tb_NoClass where ClassId ='{1}' and TimeId='{2}' and GroupId='{3}'";
                    sql = string.Format(sql, week, banjiid, shijian,n);
                    string shiyan = Helpsql.getgeti(sql);
                    if (shiyan=="" || shiyan=="不排课")
                    {
                        break;
                    }
                    sql = "select {0} from tb_NoClass where ClassId !='{1}' and TimeId='{2}'";
                    sql = string.Format(sql, week, banjiid, shijian);
                    using (SqlConnection conn=new SqlConnection(conter))
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand(sql,conn);
                        cmd.CommandTimeout = 0;
                        SqlDataReader sdr = cmd.ExecuteReader();
                        while (sdr.Read())
                        {
                            if (shiyan==sdr[0].ToString())
                            {
                                string sql1 = "select {0} from tb_NoClass where ClassId ='{1}' and TimeId='{2}' and GroupId='1'";
                                sql1 = string.Format(sql1, week, banjiid, shijian);
                                string shiyan1 = Helpsql.getgeti(sql1);
                                string sql2 = "select {0} from tb_NoClass where ClassId ='{1}' and TimeId='{2}' and GroupId='2'";
                                sql2 = string.Format(sql2, week, banjiid, shijian);
                                string shiyan2 = Helpsql.getgeti(sql2);
                                string xiangguanzhou = zhoushu(banjiid,i);
                                string sql3 = "select {0} from tb_NoClass where ClassId ='{1}' and TimeId='{2}' and GroupId='1'";
                                sql3 = string.Format(sql3, xiangguanzhou, banjiid, shijian);
                                string shiyan3 = Helpsql.getgeti(sql3);
                                string sql4 = "select {0} from tb_NoClass where ClassId ='{1}' and TimeId='{2}' and GroupId='2'";
                                sql4 = string.Format(sql4, xiangguanzhou, banjiid, shijian);
                                string shiyan4 = Helpsql.getgeti(sql4);
                                string sql5 = "update tb_NoClass set {0}='{1}'  where ClassId ='{2}' and GroupId='1'";
                                sql5 = string.Format(sql5,xiangguanzhou,shiyan1,banjiid );
                                Helpsql.getxiu(sql5);
                                string sql6 = "update tb_NoClass set {0}='{1}'  where ClassId ='{2}' and GroupId='2'";
                                sql6 = string.Format(sql6, xiangguanzhou,shiyan2, banjiid);
                                Helpsql.getxiu(sql6);
                                string sql7 = "update tb_NoClass set {0}='{1}'  where ClassId ='{2}' and GroupId='1'";
                                sql7 = string.Format(sql7, week, shiyan3, banjiid);
                                Helpsql.getxiu(sql7);
                                string sql8 = "update tb_NoClass set {0}='{1}'  where ClassId ='{2}' and GroupId='2'";
                                sql8 = string.Format(sql8, week, shiyan4, banjiid);
                                Helpsql.getxiu(sql8);
                                sdr.Close();
                                duizhuang(banjiid);
                                return;
                            }
                        }
                        sdr.Close();
                    }
                }
            }
        }
        public static string zhoushu(string banjiid,int n)
        {
            int b = 0;
            int d = 0;
            for (int i = 2; i < 17; i++)
			{
                string week = "Week" + i;
                string sql = "select count(*) from tb_NoClass where ClassId='{0}' and GroupId='1' and {1} !='不排课'";
                sql = string.Format(sql,banjiid,week);
                int c = Helpsql.getshu(sql);
                if (c == 1)
                {
                    b++;
                }
			}
            string[] str = new string[b - 1];
            for (int i = 2; i < 17; i++)
            {
                if (i==n)
                {
                    i++;
                }
                if (i < 17)
                {
                    string week = "Week" + i;
                    string sql = "select count(*) from tb_NoClass where ClassId='{0}' and GroupId='1' and {1} !='不排课'";
                    sql = string.Format(sql, banjiid, week);
                    int c = Helpsql.getshu(sql);
                    if (c == 1)
                    {
                        str[d] = week;
                        d++;
                    }
                }
            }
            Random r = new Random();
            int a = r.Next(b-1);
            return str[a].ToString();
        }
        #endregion

        //排课初始
        #region
        public static void chushi()
        {
            for (int i =2; i < 18; i++)
            {
                string week = "Week" + i;
                string sql = "update tb_NoClass set {0}='' where {0} !='不排课'";
                sql = string.Format(sql,week);
                Helpsql.getxiu(sql);
            }
        }
        #endregion
    }
}
